using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using ECommerce.DAL;


namespace Entity
{
	/// <summary>
	/// This object represents the Private Fields of TblProduct.
	/// </summary>
	
	[System.ComponentModel.DataObject]
    public partial class TblProduct : DatabaseUtility
    {
		#region TblProduct Private Fields


		Int32 _productId;
		String _productName = String.Empty;
		String _productName2 = String.Empty;
		String _productDescription = String.Empty;
		String _productImageUrl = String.Empty;
		String _displayOrder = String.Empty;
		Int32 _visitCount;
		String _productUrl = String.Empty;
		Decimal _productPrice;
		Decimal _oldPrice;
		Decimal _retailPrice;
		Decimal _additionalPrice;
		String _additionalPriceDesc = String.Empty;
		String _recipies = String.Empty;
		String _additionalInfo = String.Empty;
		DateTime _createDate;
		String _itemNumber = String.Empty;
		Boolean _isFeatured;
		Boolean _isFreeShipping;
		Boolean _isNewArrival;
		Boolean _isActive;
		Boolean _isDeleted;
		Boolean _isClearenceItem;
		Int32 _brandId;
		Int32 _categoryId;
		Decimal _currentCost;
		Decimal _standardCost;
		Boolean _isSpecial;
		
		#endregion
		#region TblProduct Constractor
		/// <summary>
        /// Creates an empty object of TblProduct. 
        /// This is mainly used to create a new instance of TblProduct to add a new TblProduct.
        /// </summary>
		public TblProduct(){}
		
				
		/// <summary>
        /// Used to retrieve employee information from database, 
        /// which is also used by Update method to save information of an existing TblProduct.
        /// </summary>
		public TblProduct
		(
			Int32 productId,
			String productName,
			String productName2,
			String productDescription,
			String productImageUrl,
			String displayOrder,
			Int32 visitCount,
			String productUrl,
			Decimal productPrice,
			Decimal oldPrice,
			Decimal retailPrice,
			Decimal additionalPrice,
			String additionalPriceDesc,
			String recipies,
			String additionalInfo,
			DateTime createDate,
			String itemNumber,
			Boolean isFeatured,
			Boolean isFreeShipping,
			Boolean isNewArrival,
			Boolean isActive,
			Boolean isDeleted,
			Boolean isClearenceItem,
			Int32 brandId,
			Int32 categoryId,
			Decimal currentCost,
			Decimal standardCost,
			Boolean isSpecial
		)
		{
			_productId = productId;	
			_productName = productName;	
			_productName2 = productName2;	
			_productDescription = productDescription;	
			_productImageUrl = productImageUrl;	
			_displayOrder = displayOrder;	
			_visitCount = visitCount;	
			_productUrl = productUrl;	
			_productPrice = productPrice;	
			_oldPrice = oldPrice;	
			_retailPrice = retailPrice;	
			_additionalPrice = additionalPrice;	
			_additionalPriceDesc = additionalPriceDesc;	
			_recipies = recipies;	
			_additionalInfo = additionalInfo;	
			_createDate = createDate;	
			_itemNumber = itemNumber;	
			_isFeatured = isFeatured;	
			_isFreeShipping = isFreeShipping;	
			_isNewArrival = isNewArrival;	
			_isActive = isActive;	
			_isDeleted = isDeleted;	
			_isClearenceItem = isClearenceItem;	
			_brandId = brandId;	
			_categoryId = categoryId;	
			_currentCost = currentCost;	
			_standardCost = standardCost;	
			_isSpecial = isSpecial;	
		}
		
		#endregion
		#region TblProduct Properties
		
		 /***********************************************************************************************
                                    -- PROPERTIES --        
         Contains all the properties representing the data fields of the current entity.
         ***********************************************************************************************/

        /// <summary>
        /// This is the Primary key value of the ProductId, which can't be assigned from the application,
        /// to ensure the data consistancy, by not letting the user to change the PK_Tbl_Product to update the data of an another TblProduct instance.
        /// For view or updation purpose, every TblProduct will be retrieved thru the proper 'Get' method.
        /// </summary>
		
		public Int32 ProductId
		{
			get {return _productId;}	
		}
		
				
		public String ProductName
		{
			get {return _productName;}
			set {_productName = value;}
		}

		public String ProductName2
		{
			get {return _productName2;}
			set {_productName2 = value;}
		}

		public String ProductDescription
		{
			get {return _productDescription;}
			set {_productDescription = value;}
		}

		public String ProductImageUrl
		{
			get {return _productImageUrl;}
			set {_productImageUrl = value;}
		}

		public String DisplayOrder
		{
			get {return _displayOrder;}
			set {_displayOrder = value;}
		}

		public Int32 VisitCount
		{
			get {return _visitCount;}
			set {_visitCount = value;}
		}

		public String ProductUrl
		{
			get {return _productUrl;}
			set {_productUrl = value;}
		}

		public Decimal ProductPrice
		{
			get {return _productPrice;}
			set {_productPrice = value;}
		}

		public Decimal OldPrice
		{
			get {return _oldPrice;}
			set {_oldPrice = value;}
		}

		public Decimal RetailPrice
		{
			get {return _retailPrice;}
			set {_retailPrice = value;}
		}

		public Decimal AdditionalPrice
		{
			get {return _additionalPrice;}
			set {_additionalPrice = value;}
		}

		public String AdditionalPriceDesc
		{
			get {return _additionalPriceDesc;}
			set {_additionalPriceDesc = value;}
		}

		public String Recipies
		{
			get {return _recipies;}
			set {_recipies = value;}
		}

		public String AdditionalInfo
		{
			get {return _additionalInfo;}
			set {_additionalInfo = value;}
		}

		public DateTime CreateDate
		{
			get {return _createDate;}
			set {_createDate = value;}
		}

		public String ItemNumber
		{
			get {return _itemNumber;}
			set {_itemNumber = value;}
		}

		public Boolean IsFeatured
		{
			get {return _isFeatured;}
			set {_isFeatured = value;}
		}

		public Boolean IsFreeShipping
		{
			get {return _isFreeShipping;}
			set {_isFreeShipping = value;}
		}

		public Boolean IsNewArrival
		{
			get {return _isNewArrival;}
			set {_isNewArrival = value;}
		}

		public Boolean IsActive
		{
			get {return _isActive;}
			set {_isActive = value;}
		}

		public Boolean IsDeleted
		{
			get {return _isDeleted;}
			set {_isDeleted = value;}
		}

		public Boolean IsClearenceItem
		{
			get {return _isClearenceItem;}
			set {_isClearenceItem = value;}
		}

		public Int32 BrandId
		{
			get {return _brandId;}
			set {_brandId = value;}
		}

		public Int32 CategoryId
		{
			get {return _categoryId;}
			set {_categoryId = value;}
		}

		public Decimal CurrentCost
		{
			get {return _currentCost;}
			set {_currentCost = value;}
		}

		public Decimal StandardCost
		{
			get {return _standardCost;}
			set {_standardCost = value;}
		}

		public Boolean IsSpecial
		{
			get {return _isSpecial;}
			set {_isSpecial = value;}
		}
		

		#endregion
		#region TblProduct Static Methods
		/************************************************************************************
                                    -- CREATE METHOD (STATIC) --
        *************************************************************************************/
		/************************* Architecure note:**********************************
        Note 1:
        This method is specially useful for the case, where we need to use Object Data Source controls.
        For ObjectDataSource/SqlDataSource controls, the insert/update metnod requires paramters methods, rather property based methods.
        However, there is still a way to pass property based objects as method parameter for data source controls, 
        but in that case we could not use Sql type, as view controls or asp parameter control can only contain few of primitive type.
        The main reason to remain the Sql Type in our object architecture is it is faster and contains better way for null value support.
        
        Note 2: 
        Non-primary key: it should be primitive type, as the asp paramter control (included in data source control) supports only primitive types.
        /*****************************************************************************/
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        public static  Int32 CreateNewTblProduct
		(
			String productName,
			String productName2,
			String productDescription,
			String productImageUrl,
			String displayOrder,
			Int32 visitCount,
			String productUrl,
			Decimal productPrice,
			Decimal oldPrice,
			Decimal retailPrice,
			Decimal additionalPrice,
			String additionalPriceDesc,
			String recipies,
			String additionalInfo,
			DateTime createDate,
			String itemNumber,
			Boolean isFeatured,
			Boolean isFreeShipping,
			Boolean isNewArrival,
			Boolean isActive,
			Boolean isDeleted,
			Boolean isClearenceItem,
			Int32 brandId,
			Int32 categoryId,
			Decimal currentCost,
			Decimal standardCost,
			Boolean isSpecial
		)
		{
			//creating an empty TblProduct object
            TblProduct tblProduct = new TblProduct();
				tblProduct.ProductName = productName;
				tblProduct.ProductName2 = productName2;
				tblProduct.ProductDescription = productDescription;
				tblProduct.ProductImageUrl = productImageUrl;
				tblProduct.DisplayOrder = displayOrder;
				tblProduct.VisitCount = visitCount;
				tblProduct.ProductUrl = productUrl;
				tblProduct.ProductPrice = productPrice;
				tblProduct.OldPrice = oldPrice;
				tblProduct.RetailPrice = retailPrice;
				tblProduct.AdditionalPrice = additionalPrice;
				tblProduct.AdditionalPriceDesc = additionalPriceDesc;
				tblProduct.Recipies = recipies;
				tblProduct.AdditionalInfo = additionalInfo;
				tblProduct.CreateDate = createDate;
				tblProduct.ItemNumber = itemNumber;
				tblProduct.IsFeatured = isFeatured;
				tblProduct.IsFreeShipping = isFreeShipping;
				tblProduct.IsNewArrival = isNewArrival;
				tblProduct.IsActive = isActive;
				tblProduct.IsDeleted = isDeleted;
				tblProduct.IsClearenceItem = isClearenceItem;
				tblProduct.BrandId = brandId;
				tblProduct.CategoryId = categoryId;
				tblProduct.CurrentCost = currentCost;
				tblProduct.StandardCost = standardCost;
				tblProduct.IsSpecial = isSpecial;
			
			return TblProduct.CreateNewTblProduct(tblProduct);
		}
	
		/************************************************************************************
                 -- UPDATE METHODS (STATIC) --
        *************************************************************************************/

        /************************* Architecure note:**********************************
        Note 1:
        This method is specially useful for the case, where we need to use Object Data Source controls.
        For ObjectDataSource/SqlDataSource controls, the insert/update metnod requires paramters methods, rather property based methods.
        However, there is still a way to pass property based objects as method parameter for data source controls, 
        but in that case we could not use Sql type, as view controls or asp parameter control can only contain few of primitive type.
        The main reason to remain the Sql Type in our object architecture is it is faster and contains better way for null value support.
        
        Note 2: 
        There the two types of paramaters will be passed here.
         * a. Primary key paramater: it should be Sql type, as we will bind the primary key property for the view controls (form view, details view etc) as datakey.
         * b. Non-primary key: it should be primitive type, as the asp paramter control (included in data source control) supports only primitive types.
        /*****************************************************************************/
		
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static  bool UpdateTblProduct
		(
			Int32 productId,
			String productName,
			String productName2,
			String productDescription,
			String productImageUrl,
			String displayOrder,
			Int32 visitCount,
			String productUrl,
			Decimal productPrice,
			Decimal oldPrice,
			Decimal retailPrice,
			Decimal additionalPrice,
			String additionalPriceDesc,
			String recipies,
			String additionalInfo,
			DateTime createDate,
			String itemNumber,
			Boolean isFeatured,
			Boolean isFreeShipping,
			Boolean isNewArrival,
			Boolean isActive,
			Boolean isDeleted,
			Boolean isClearenceItem,
			Int32 brandId,
			Int32 categoryId,
			Decimal currentCost,
			Decimal standardCost,
			Boolean isSpecial
		)
		{
			 TblProduct tblProduct =  TblProduct.GetTblProductByProductId(productId);
			
				tblProduct.ProductName = productName;
				tblProduct.ProductName2 = productName2;
				tblProduct.ProductDescription = productDescription;
				tblProduct.ProductImageUrl = productImageUrl;
				tblProduct.DisplayOrder = displayOrder;
				tblProduct.VisitCount = visitCount;
				tblProduct.ProductUrl = productUrl;
				tblProduct.ProductPrice = productPrice;
				tblProduct.OldPrice = oldPrice;
				tblProduct.RetailPrice = retailPrice;
				tblProduct.AdditionalPrice = additionalPrice;
				tblProduct.AdditionalPriceDesc = additionalPriceDesc;
				tblProduct.Recipies = recipies;
				tblProduct.AdditionalInfo = additionalInfo;
				tblProduct.CreateDate = createDate;
				tblProduct.ItemNumber = itemNumber;
				tblProduct.IsFeatured = isFeatured;
				tblProduct.IsFreeShipping = isFreeShipping;
				tblProduct.IsNewArrival = isNewArrival;
				tblProduct.IsActive = isActive;
				tblProduct.IsDeleted = isDeleted;
				tblProduct.IsClearenceItem = isClearenceItem;
				tblProduct.BrandId = brandId;
				tblProduct.CategoryId = categoryId;
				tblProduct.CurrentCost = currentCost;
				tblProduct.StandardCost = standardCost;
				tblProduct.IsSpecial = isSpecial;
			
			return TblProduct.UpdateTblProduct(tblProduct);
		}

		/************************************************************************************
                         -- DELETE METHODS --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        We have used the employeeId parameter as SqlInt32, to bind the delete method with object data source and view control directly.
        ****************************************************************************/
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static  bool DeleteTblProduct
		(
			Int32 productId
		)
		{
			//Validate Input
			if(productId <= GetProductIdMinValue)
				throw (new ArgumentOutOfRangeException("productId"));
				
			// Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
			AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
			AddParameterToSqlCmd(sqlCmd, "@ProductId",SqlDbType.Int, 0, ParameterDirection.Input, productId);
			SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_DELETETBLPRODUCT);
			ExecuteScalarCmd(sqlCmd);
			int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
			return (returnValue == 0 ? false : true);
			
		}
		
		/// <summary>
        /// Deletes a set of employees from the database.
        /// </summary>
		public static bool DeleteTblProductList( System.Collections.Generic.List<Int32>  productIdListToDelete)
		{
			//Validate Input
			foreach (Int32 productId in productIdListToDelete)
			    if(productId <= GetProductIdMinValue)
					throw (new ArgumentOutOfRangeException("productId"));
			
			//performing deletion operation //

            string xmlString = FormatXMLForIdArray(productIdListToDelete);
			
			// Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
			AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ProductIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
			SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_DELETETBLPRODUCTLIST);
			ExecuteScalarCmd(sqlCmd);
			int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
			return (returnValue == productIdListToDelete.Count ? true : false);
		}
		
		/************************************************************************************
                                 -- GET BY PRIMARY METHODS --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        Retrives an Employee from database, based on it's primary key.
        To view or update an employee, everytime we must have to use this type of 'get' method, 
        since we don't have direct access to the Primary key value for assigning value.
        We have used the employeeId parameter as SqlInt32, to bind the delete method with object data source and view control directly.
        /*****************************************************************************/
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
		public static TblProduct GetTblProductByProductId(Int32 productId)
		{
			if(productId <= GetProductIdMinValue)
					throw (new ArgumentOutOfRangeException("productId"));
				
		 	// Execute SQL Command
			SqlCommand sqlCmd = new SqlCommand();
			AddParameterToSqlCmd(sqlCmd, "@ProductId",SqlDbType.Int, 0, ParameterDirection.Input, productId);
			SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_GETTBLPRODUCTBYPRODUCTID);
			GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
		}
		
		/************************************************************************************
                         -- GET BY FOREIGN KEY METHODS --
        *************************************************************************************/
		
		/************************************************************************************
                 -- GET ALL METHODS --
        *************************************************************************************/
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static CustomCollection<TblProduct> GetAllTblProduct()
		{
		
		 	// Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_GETALLTBLPRODUCT);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
		}
		
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static CustomCollection<TblProduct> GetAllTblProductPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ProductId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReader);
            CustomCollection<TblProduct> objCollection = ((CustomCollection<TblProduct>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }
		
		[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static int GetAllTblProductPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
		
		#endregion
		#region Employee Instance Methods
		/************************************************************************************
                                    -- SAVE (INSERT/UPDATE) METHODS (INSTANCE) --
        *************************************************************************************/
		/// <summary>
        /// Saves (inserts and updates) an Employee into database.
        /// The insertion occur if the EmployeeId conatins default value.
        /// Otherwises it updates the Employee into the database, based on the primary key value stored in EmployeeId.
        /// This technique ensures an Employee will not be updated without having a valid EmployeeId.
        /// </summary>
		public bool Save()
		{
			if(ProductId<= GetProductIdMinValue)
			{
				Int32 TempId=CreateNewTblProduct(this);
				if(TempId>0)
				{
					_productId=TempId;
					return true;
				}
				else
				{
					// error occurs
					return false;
				}
			}
			else
			{	//This is a update Command
				return (UpdateTblProduct(this));
			}
			
		}
		
		public static Int32 CreateNewTblProduct(TblProduct newTblProduct)
		{
			// validate Parameters
			if (newTblProduct == null)
				throw (new ArgumentNullException("newTblProduct"));

			// Execute SQL Command
			SqlCommand sqlCmd = new SqlCommand();
			int length = 0;
			AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            
			AddParameterToSqlCmd(sqlCmd, "@ProductName",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductName);
			AddParameterToSqlCmd(sqlCmd, "@ProductName2",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductName2);
            AddParameterToSqlCmd(sqlCmd, "@ProductDescription", SqlDbType.Text, newTblProduct.ProductDescription.Length, ParameterDirection.Input, newTblProduct.ProductDescription);
			AddParameterToSqlCmd(sqlCmd, "@ProductImageUrl",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductImageUrl);
			AddParameterToSqlCmd(sqlCmd, "@DisplayOrder",SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.DisplayOrder);
			AddParameterToSqlCmd(sqlCmd, "@VisitCount",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.VisitCount);
			AddParameterToSqlCmd(sqlCmd, "@ProductUrl",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductUrl);
			AddParameterToSqlCmd(sqlCmd, "@ProductPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.ProductPrice);
			AddParameterToSqlCmd(sqlCmd, "@OldPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.OldPrice);
			AddParameterToSqlCmd(sqlCmd, "@RetailPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.RetailPrice);
			AddParameterToSqlCmd(sqlCmd, "@AdditionalPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.AdditionalPrice);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalPriceDesc", SqlDbType.NVarChar, newTblProduct.AdditionalPriceDesc.Length, ParameterDirection.Input, newTblProduct.AdditionalPriceDesc);
            AddParameterToSqlCmd(sqlCmd, "@Recipies", SqlDbType.NVarChar, newTblProduct.Recipies.Length, ParameterDirection.Input, newTblProduct.Recipies);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalInfo", SqlDbType.NVarChar, newTblProduct.AdditionalInfo.Length, ParameterDirection.Input, newTblProduct.AdditionalInfo);
			AddParameterToSqlCmd(sqlCmd, "@CreateDate",SqlDbType.DateTime, 0, ParameterDirection.Input, newTblProduct.CreateDate);
			AddParameterToSqlCmd(sqlCmd, "@ItemNumber",SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.ItemNumber);
			AddParameterToSqlCmd(sqlCmd, "@IsFeatured",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsFeatured);
			AddParameterToSqlCmd(sqlCmd, "@IsFreeShipping",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsFreeShipping);
			AddParameterToSqlCmd(sqlCmd, "@IsNewArrival",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsNewArrival);
			AddParameterToSqlCmd(sqlCmd, "@IsActive",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsActive);
			AddParameterToSqlCmd(sqlCmd, "@IsDeleted",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsDeleted);
			AddParameterToSqlCmd(sqlCmd, "@IsClearenceItem",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsClearenceItem);
			AddParameterToSqlCmd(sqlCmd, "@BrandId",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.BrandId);
			AddParameterToSqlCmd(sqlCmd, "@CategoryId",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.CategoryId);
			AddParameterToSqlCmd(sqlCmd, "@CurrentCost",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.CurrentCost);
			AddParameterToSqlCmd(sqlCmd, "@StandardCost",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.StandardCost);
			AddParameterToSqlCmd(sqlCmd, "@IsSpecial",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsSpecial);
			
			SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_CREATENEWTBLPRODUCT);
			ExecuteScalarCmd(sqlCmd);
			return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

		}
		/// <summary>
        /// Updates an TblProduct instance to the database.
        /// </summary>
		public static bool UpdateTblProduct(TblProduct newTblProduct)
		{
			// validate Parameters
			if (newTblProduct == null)
				throw (new ArgumentNullException("newTblProduct"));

			// Execute SQL Command
			SqlCommand sqlCmd = new SqlCommand();
			int length = 0;
			AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            
			AddParameterToSqlCmd(sqlCmd, "@ProductId",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.ProductId);
			AddParameterToSqlCmd(sqlCmd, "@ProductName",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductName);
			AddParameterToSqlCmd(sqlCmd, "@ProductName2",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductName2);
            AddParameterToSqlCmd(sqlCmd, "@ProductDescription", SqlDbType.Text, newTblProduct.ProductDescription.Length, ParameterDirection.Input, newTblProduct.ProductDescription);
			AddParameterToSqlCmd(sqlCmd, "@ProductImageUrl",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductImageUrl);
			AddParameterToSqlCmd(sqlCmd, "@DisplayOrder",SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.DisplayOrder);
			AddParameterToSqlCmd(sqlCmd, "@VisitCount",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.VisitCount);
			AddParameterToSqlCmd(sqlCmd, "@ProductUrl",SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProduct.ProductUrl);
			AddParameterToSqlCmd(sqlCmd, "@ProductPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.ProductPrice);
			AddParameterToSqlCmd(sqlCmd, "@OldPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.OldPrice);
			AddParameterToSqlCmd(sqlCmd, "@RetailPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.RetailPrice);
			AddParameterToSqlCmd(sqlCmd, "@AdditionalPrice",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.AdditionalPrice);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalPriceDesc", SqlDbType.NVarChar, newTblProduct.AdditionalPriceDesc.Length, ParameterDirection.Input, newTblProduct.AdditionalPriceDesc);
            AddParameterToSqlCmd(sqlCmd, "@Recipies", SqlDbType.NVarChar, newTblProduct.Recipies.Length, ParameterDirection.Input, newTblProduct.Recipies);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalInfo", SqlDbType.NVarChar, newTblProduct.AdditionalInfo.Length, ParameterDirection.Input, newTblProduct.AdditionalInfo);
			AddParameterToSqlCmd(sqlCmd, "@CreateDate",SqlDbType.DateTime, 0, ParameterDirection.Input, newTblProduct.CreateDate);
			AddParameterToSqlCmd(sqlCmd, "@ItemNumber",SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProduct.ItemNumber);
			AddParameterToSqlCmd(sqlCmd, "@IsFeatured",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsFeatured);
			AddParameterToSqlCmd(sqlCmd, "@IsFreeShipping",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsFreeShipping);
			AddParameterToSqlCmd(sqlCmd, "@IsNewArrival",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsNewArrival);
			AddParameterToSqlCmd(sqlCmd, "@IsActive",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsActive);
			AddParameterToSqlCmd(sqlCmd, "@IsDeleted",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsDeleted);
			AddParameterToSqlCmd(sqlCmd, "@IsClearenceItem",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsClearenceItem);
			AddParameterToSqlCmd(sqlCmd, "@BrandId",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.BrandId);
			AddParameterToSqlCmd(sqlCmd, "@CategoryId",SqlDbType.Int, 0, ParameterDirection.Input, newTblProduct.CategoryId);
			AddParameterToSqlCmd(sqlCmd, "@CurrentCost",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.CurrentCost);
			AddParameterToSqlCmd(sqlCmd, "@StandardCost",SqlDbType.Decimal, 9, ParameterDirection.Input, newTblProduct.StandardCost);
			AddParameterToSqlCmd(sqlCmd, "@IsSpecial",SqlDbType.Bit, 0, ParameterDirection.Input, newTblProduct.IsSpecial);
			
			SetCommandType(sqlCmd,CommandType.StoredProcedure,SPR_TBLPRODUCT_UPDATETBLPRODUCT);
            ExecuteScalarCmd(sqlCmd);
			int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
			return (returnValue == 0 ? false : true);
		}
		#endregion
		
		#region TblProduct Related Collections
		/// <summary>
        /// Creates and returns a strongly typed collection of Employee custom entity. 
        /// The colelction is created through iterating on the IdataReader object which contains Employee information, as a set of records, similar to tabular format.
        /// </summary>
		public static CollectionBase  GenerateTblProductCollectionFromReader(IDataReader returnData) 
		{
			
			//creating the instance of Employee collection
			CustomCollection<TblProduct> colTblProduct = new CustomCollection<TblProduct>();
			
			/************************* Architecure note:**********************************
             * Below code includes the null value functionality to retieve the data which has nill value in database end.
             * Design consideration:
             * Besides general data fields, special care should be taken for primary keys, to assign '0'/defaultvalue, rather passing 'Null' value in constructor parameter.
             * Although we are considering sqldb tyoe data for the current data container, but while retireving data from database end, through datareader object, we need to cast data reader objects using .net primitive data type, 
             * rather using sqldb type to cast, since data reader objects don't support sql db type to be casted.
             *****************************************************************************/

            //Iterating thriugh the data reader, to generate Employee collection.

			//getting the Employee collection by iterating thru the data reader
			while (returnData.Read()) 
			{
				//passing the TblProduct constructor parameters from the current instance of data reader fields.
				TblProduct newTblProduct = new TblProduct
				(
					returnData["ProductId"]== System.DBNull.Value ? GetProductIdMinValue : (Int32 ) returnData["ProductId"],
					returnData["ProductName"]== System.DBNull.Value ? string.Empty: (String ) returnData["ProductName"],
					returnData["ProductName2"]== System.DBNull.Value ? string.Empty: (String ) returnData["ProductName2"],
					returnData["ProductDescription"]== System.DBNull.Value ? null: (String ) returnData["ProductDescription"],
					returnData["ProductImageUrl"]== System.DBNull.Value ? string.Empty: (String ) returnData["ProductImageUrl"],
					returnData["DisplayOrder"]== System.DBNull.Value ? string.Empty: (String ) returnData["DisplayOrder"],
					returnData["VisitCount"]== System.DBNull.Value ? 0: (Int32 ) returnData["VisitCount"],
					returnData["ProductUrl"]== System.DBNull.Value ? string.Empty: (String ) returnData["ProductUrl"],
					returnData["ProductPrice"]== System.DBNull.Value ? 0: (Decimal ) returnData["ProductPrice"],
					returnData["OldPrice"]== System.DBNull.Value ? 0: (Decimal ) returnData["OldPrice"],
					returnData["RetailPrice"]== System.DBNull.Value ? 0: (Decimal ) returnData["RetailPrice"],
					returnData["AdditionalPrice"]== System.DBNull.Value ? 0: (Decimal ) returnData["AdditionalPrice"],
					returnData["AdditionalPriceDesc"]== System.DBNull.Value ? string.Empty: (String ) returnData["AdditionalPriceDesc"],
					returnData["Recipies"]== System.DBNull.Value ? string.Empty: (String ) returnData["Recipies"],
					returnData["AdditionalInfo"]== System.DBNull.Value ? string.Empty: (String ) returnData["AdditionalInfo"],
					returnData["CreateDate"]== System.DBNull.Value ? DateTime.MinValue: (DateTime ) returnData["CreateDate"],
					returnData["ItemNumber"]== System.DBNull.Value ? string.Empty: (String ) returnData["ItemNumber"],
					returnData["IsFeatured"]== System.DBNull.Value ? false: (Boolean ) returnData["IsFeatured"],
					returnData["IsFreeShipping"]== System.DBNull.Value ? false: (Boolean ) returnData["IsFreeShipping"],
					returnData["IsNewArrival"]== System.DBNull.Value ? false: (Boolean ) returnData["IsNewArrival"],
					returnData["IsActive"]== System.DBNull.Value ? false: (Boolean ) returnData["IsActive"],
					returnData["IsDeleted"]== System.DBNull.Value ? false: (Boolean ) returnData["IsDeleted"],
					returnData["IsClearenceItem"]== System.DBNull.Value ? false: (Boolean ) returnData["IsClearenceItem"],
					returnData["BrandId"]== System.DBNull.Value ? 0: (Int32 ) returnData["BrandId"],
					returnData["CategoryId"]== System.DBNull.Value ? 0: (Int32 ) returnData["CategoryId"],
					returnData["CurrentCost"]== System.DBNull.Value ? 0: (Decimal ) returnData["CurrentCost"],
					returnData["StandardCost"]== System.DBNull.Value ? 0: (Decimal ) returnData["StandardCost"],
					returnData["IsSpecial"]== System.DBNull.Value ? false: (Boolean ) returnData["IsSpecial"]

				);
				//adding the Employee to the collection
				colTblProduct.Add(newTblProduct);
			}
			
			//returns the collection of Companys 
			return (colTblProduct);
		}
		
		#endregion
		#region Constants and Default Values
		
		public static Int32 GetProductIdMinValue
		{
			get
			{
				return 0;
			}
		}
		
		//create method related contants
			internal const string SPR_TBLPRODUCT_CREATENEWTBLPRODUCT="spr_TblProduct_CreateNewTblProduct";
			internal const string SPR_TBLPRODUCT_UPDATETBLPRODUCT="spr_TblProduct_UpdateTblProduct";
			internal const string SPR_TBLPRODUCT_DELETETBLPRODUCT="spr_TblProduct_DeleteTblProduct";
			internal const string SPR_TBLPRODUCT_DELETETBLPRODUCTLIST="spr_TblProduct_DeleteTblProductList";
			internal const string SPR_TBLPRODUCT_GETTBLPRODUCTBYPRODUCTID="spr_TblProduct_GetTblProductByProductId";
			internal const string SPR_TBLPRODUCT_GETALLTBLPRODUCT="spr_TblProduct_GetAllTblProduct";
			internal const string SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGED="spr_TblProduct_GetAllTblProductPaged";
			internal const string SPR_TBLPRODUCT_GETALLTBLPRODUCTPAGEDCOUNT="spr_TblProduct_GetAllTblProductPagedCount";
		
		#endregion
	}
}	
